#!/usr/bin/env python3
"""
创建数据库表和插入测试数据
"""

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from datetime import datetime, timezone
from app.core.database import Base
from app.models.hackathon import Hackathon
from app.core.config import settings

# 使用配置中的数据库 URL
DATABASE_URL = settings.DATABASE_URL
if DATABASE_URL.startswith("sqlite://"):
    DATABASE_URL = DATABASE_URL.replace("sqlite://", "sqlite+aiosqlite://")
elif DATABASE_URL.startswith("postgresql://"):
    DATABASE_URL = DATABASE_URL.replace("postgresql://", "postgresql+asyncpg://")

async def create_tables_and_data():
    """创建表和插入测试数据"""
    
    # 创建异步引擎
    engine = create_async_engine(DATABASE_URL, echo=True)
    
    # 创建会话
    async_session = sessionmaker(
        engine, class_=AsyncSession, expire_on_commit=False
    )
    
    try:
        # 创建所有表
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
        
        print("✅ 数据库表创建成功")
        
        # 插入测试数据
        async with async_session() as session:
            # 检查是否已有数据
            from sqlalchemy import text
            result = await session.execute(
                text("SELECT COUNT(*) FROM competitions")
            )
            count = result.scalar()
            
            if count == 0:
                # 创建测试数据
                from app.models.hackathon import CompetitionType, CompetitionStatus, DifficultyLevel
                
                test_competitions = [
                    Hackathon(
                        title="2024 AI 创新挑战赛",
                        description="探索人工智能在各个领域的创新应用，打造下一代智能解决方案",
                        type=CompetitionType.HACKATHON,
                        platform="Kaggle",
                        prize_pool="50000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 3, 15, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=1250,
                        difficulty=DifficultyLevel.ADVANCED,
                        category="AI/ML",
                        tags=["AI", "机器学习", "深度学习"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=AI%20innovation%20challenge%20competition%20poster%20with%20futuristic%20technology%20elements&image_size=landscape_4_3",
                        external_url="https://www.kaggle.com/competitions/ai-innovation-2024",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="全栈开发马拉松",
                        description="48小时内构建完整的全栈应用，展示你的编程技能和创意思维",
                        type=CompetitionType.HACKATHON,
                        platform="DevPost",
                        prize_pool="30000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 2, 28, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=890,
                        difficulty=DifficultyLevel.INTERMEDIATE,
                        category="Web开发",
                        tags=["全栈", "React", "Node.js"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=full%20stack%20development%20hackathon%20coding%20marathon%20programming&image_size=landscape_4_3",
                        external_url="https://devpost.com/hackathons",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="区块链创新大赛",
                        description="利用区块链技术解决现实世界问题，推动去中心化应用发展",
                        type=CompetitionType.HACKATHON,
                        platform="GitHub",
                        prize_pool="80000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 4, 20, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=567,
                        difficulty=DifficultyLevel.ADVANCED,
                        category="区块链",
                        tags=["区块链", "DeFi", "Web3"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=blockchain%20innovation%20competition%20cryptocurrency%20decentralized%20technology&image_size=landscape_4_3",
                        external_url="https://github.com/blockchain-hackathon",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="移动应用设计挑战",
                        description="设计并开发创新的移动应用，提升用户体验和生活质量",
                        type=CompetitionType.DESIGN_CONTEST,
                        platform="App Store",
                        prize_pool="25000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 3, 10, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=1100,
                        difficulty=DifficultyLevel.INTERMEDIATE,
                        category="移动开发",
                        tags=["iOS", "Android", "Flutter"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=mobile%20app%20design%20challenge%20smartphone%20user%20interface&image_size=landscape_4_3",
                        external_url="https://developer.apple.com/app-store/",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="数据科学竞赛",
                        description="分析复杂数据集，发现隐藏的模式和洞察，解决商业问题",
                        type=CompetitionType.DATA_SCIENCE,
                        platform="Kaggle",
                        prize_pool="40000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 3, 25, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=2300,
                        difficulty=DifficultyLevel.INTERMEDIATE,
                        category="数据科学",
                        tags=["数据分析", "Python", "机器学习"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=data%20science%20competition%20analytics%20charts%20graphs%20statistics&image_size=landscape_4_3",
                        external_url="https://www.kaggle.com/competitions",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="游戏开发挑战赛",
                        description="创造令人惊叹的游戏体验，展示你的创意和技术实力",
                        type=CompetitionType.CODING_CHALLENGE,
                        platform="Unity",
                        prize_pool="35000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 4, 5, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=750,
                        difficulty=DifficultyLevel.ADVANCED,
                        category="游戏开发",
                        tags=["Unity", "C#", "游戏设计"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=game%20development%20challenge%20video%20games%20programming%20unity&image_size=landscape_4_3",
                        external_url="https://unity.com/competitions",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="网络安全挑战",
                        description="测试你的网络安全技能，发现并修复安全漏洞",
                        type=CompetitionType.CODING_CHALLENGE,
                        platform="HackerOne",
                        prize_pool="60000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 3, 30, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=450,
                        difficulty=DifficultyLevel.ADVANCED,
                        category="网络安全",
                        tags=["安全", "渗透测试", "CTF"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=cybersecurity%20challenge%20hacking%20security%20shield%20protection&image_size=landscape_4_3",
                        external_url="https://www.hackerone.com/",
                        status=CompetitionStatus.ONGOING
                    ),
                    Hackathon(
                        title="UI/UX 设计大赛",
                        description="设计出色的用户界面和用户体验，提升产品的可用性",
                        type=CompetitionType.DESIGN_CONTEST,
                        platform="Dribbble",
                        prize_pool="20000",
                        prize_currency="CNY",
                        deadline=datetime(2024, 2, 25, 23, 59, 59, tzinfo=timezone.utc),
                        participants_count=980,
                        difficulty=DifficultyLevel.BEGINNER,
                        category="UI/UX",
                        tags=["设计", "Figma", "用户体验"],
                        banner_image="https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=UI%20UX%20design%20competition%20user%20interface%20creative%20design&image_size=landscape_4_3",
                        external_url="https://dribbble.com/shots",
                        status=CompetitionStatus.ONGOING
                    )
                ]
                
                # 添加到会话
                for comp in test_competitions:
                    session.add(comp)
                
                # 提交事务
                await session.commit()
                
                print(f"✅ 成功插入 {len(test_competitions)} 条测试数据")
            else:
                print(f"ℹ️ 数据库中已有 {count} 条竞赛记录")
        
        print("✅ 数据库初始化完成")
        
    except Exception as e:
        print(f"❌ 数据库初始化失败: {e}")
        raise
    finally:
        await engine.dispose()

if __name__ == "__main__":
    asyncio.run(create_tables_and_data())